﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
namespace DAO
{
    public class HoaDonDAO
    {
        // -------------- LOI ----------------------
        public List<HoaDonDTO> LayDanhSachHoaDonL(int loaiHD)//0 đơn đặt hàng   1. hóa đơn 2. tất cả
        {
            List<HoaDonDTO> listHD = new List<HoaDonDTO>();
            string sql = "";
            if (loaiHD == 2)
            {
                sql = "Select * From HOADON Where LoaiHoaDon = 0";
            }
            else
            {
                if (loaiHD == 1)
                {
                    sql = "Select * From HOADON Where LoaiHoaDon = 1";
                }
                else
                {
                    sql = "Select * From HOADON";
                }
            }
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHoaDon = reader.GetInt32(0);
                hdDTO.MaKhachHang = reader.GetInt32(1);
                hdDTO.NgayLap = reader.GetDateTime(2);
                hdDTO.Vat = reader.GetString(3);
                hdDTO.NgayGiao = reader.GetDateTime(4);
                hdDTO.TenNguoiNhan = reader.GetString(5);
                hdDTO.SdtNguoiNhan = reader.GetString(6);
                hdDTO.MaSoThue = reader.GetString(7);
                hdDTO.GiaoDen = reader.GetString(8);
                hdDTO.TraTruoc = reader.GetDouble(9);
                hdDTO.ConNo = reader.GetDouble(10);
                hdDTO.PhiGiaoHang = reader.GetDouble(11);
                hdDTO.ThanhTien = reader.GetDouble(12);
                hdDTO.TinhTrang = reader.GetInt32(13);
                hdDTO.LoaiHoaDon = reader.GetInt32(14);
                listHD.Add(hdDTO);
            }
            conn.Close();
            return listHD;
        }
        public string ThemHoaDonL(HoaDonDTO hdDTO)
        {
            string mess = "";

            string sql = "Insert into";
            sql += " HOADON(MaHoaDon, MaKhachHang, NgayLap, Vat, NgayGiao, TenNguoiNhan, SdtNguoiNhan, MaSoThue,GiaoDen, TraTruoc, ConNo, PhiGiaoHang, ThanhTien, TinhTrang, LoaiHoaDon)";
            sql += " values(@MaHoaDon, @MaKhachHang, @NgayLap, @Vat, @NgayGiao, @TenNguoiNhan, @SdtNguoiNhan, @MaSoThue, @GiaoDen, @TraTruoc, @ConNo, @PhiGiaoHang, @ThanhTien, @TinhTrang, @LoaiHoaDon)";
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlParameter para;
                para = cmd.Parameters.Add("@MaHoaDon", SqlDbType.Int);
                para.Value = hdDTO.MaHoaDon;
                para = cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                para.Value = hdDTO.MaKhachHang;
                para = cmd.Parameters.Add("@NgayLap", SqlDbType.DateTime);
                para.Value = hdDTO.NgayLap;
                para = cmd.Parameters.Add("@Vat", SqlDbType.NChar);
                para.Value = hdDTO.Vat;
                para = cmd.Parameters.Add("@NgayGiao", SqlDbType.DateTime);
                para.Value = hdDTO.NgayGiao;
                para = cmd.Parameters.Add("@TenNguoiNhan", SqlDbType.NVarChar);
                para.Value = hdDTO.TenNguoiNhan;
                para = cmd.Parameters.Add("@SdtNguoiNhan", SqlDbType.VarChar);
                para.Value = hdDTO.SdtNguoiNhan;
                para = cmd.Parameters.Add("@MaSoThue", SqlDbType.NChar);
                para.Value = hdDTO.MaSoThue;
                para = cmd.Parameters.Add("@GiaoDen", SqlDbType.NVarChar);
                para.Value = hdDTO.GiaoDen;
                para = cmd.Parameters.Add("@TraTruoc", SqlDbType.Float);
                para.Value = hdDTO.TraTruoc;
                para = cmd.Parameters.Add("@ConNo", SqlDbType.Float);
                para.Value = hdDTO.ConNo;
                para = cmd.Parameters.Add("@PhiGiaoHang", SqlDbType.Float);
                para.Value = hdDTO.PhiGiaoHang;
                para = cmd.Parameters.Add("@ThanhTien", SqlDbType.Float);
                para.Value = hdDTO.ThanhTien;
                para = cmd.Parameters.Add("@TinhTrang", SqlDbType.Int);
                para.Value = hdDTO.TinhTrang;
                para = cmd.Parameters.Add("@LoaiHoaDon", SqlDbType.Int);
                para.Value = hdDTO.LoaiHoaDon;
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }

        public int LayMaHoaDonL()
        {
            int max = 0;
            List<HoaDonDTO> danhsach = new List<HoaDonDTO>();
            string sql = "Select * From HOADON";
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                int ma = reader.GetInt32(0);
                if (ma > max)
                    max = ma;
            }
            conn.Close();
            return max;
        }
        public string XoaHoaDonL(int maHD)
        {
            string mess = "";
            string sql = "Delete * From HOADON where MaHoaDon = " + maHD;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }
        public HoaDonDTO ThongTinHoaDonTheoMaL(int maHD)
        {
            string sql = "Select * From HOADON Where MaHoaDon = " + maHD;
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            HoaDonDTO hdDTO = new HoaDonDTO();
            while (reader.Read())
            {
                hdDTO.MaHoaDon = reader.GetInt32(0);
                hdDTO.MaKhachHang = reader.GetInt32(1);
                hdDTO.NgayLap = reader.GetDateTime(2);
                hdDTO.Vat = reader.GetString(3);
                hdDTO.NgayGiao = reader.GetDateTime(4);
                hdDTO.TenNguoiNhan = reader.GetString(5);
                hdDTO.SdtNguoiNhan = reader.GetString(6);
                hdDTO.MaSoThue = reader.GetString(7);
                hdDTO.GiaoDen = reader.GetString(8);
                hdDTO.TraTruoc = reader.GetDouble(9);
                hdDTO.ConNo = reader.GetDouble(10);
                hdDTO.PhiGiaoHang = reader.GetDouble(11);
                hdDTO.ThanhTien = reader.GetDouble(12);
                hdDTO.TinhTrang = reader.GetInt32(13);
                hdDTO.LoaiHoaDon = reader.GetInt32(14);
            }
            conn.Close();
            return hdDTO;
        }
        public long LayThoiGianGiaoL(int maHD)
        {
            string sql = "select datediff(n,getdate(),NgayGiao) From HOADON Where MaHoaDon =  " + maHD;
            long phut = 0;
            SqlConnection cn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand(sql, cn);
            SqlDataReader read = cm.ExecuteReader();
            while (read.Read())
            {
                phut = read.GetInt32(0);
            }
            return phut;
        }
        public string CapNhatTinhTrangL(int maHD, int tt)
        {
            string mess = "";
            string sql = "Update HOADON SET TinhTrang = " + tt + " Where MaHoaDon = " + maHD;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        
        }
        //END ------------ LOI ------------------ 

        //KHANG 
        public string KetNoi()
        {
            string kq = "";
            SqlConnection conn = DataProvider.ConnectDB();
            if (conn == null)
                kq = "THat bai";
            else
                kq = "Thanh cong";
            conn.Close();
            return kq;

        }
        public List<HoaDonDTO> LayDanhSachHoaDon()
        {
            List<HoaDonDTO> danhsach = new List<HoaDonDTO>();
            string sql = "Select * From HOADON";

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHoaDon = reader.GetInt32(0);
                hdDTO.MaKhachHang = reader.GetInt32(1);
                hdDTO.NgayLap = reader.GetDateTime(2);
                hdDTO.Vat = reader.GetString(3);
                hdDTO.NgayGiao = reader.GetDateTime(4);
                hdDTO.TenNguoiNhan = reader.GetString(5);
                hdDTO.SdtNguoiNhan = reader.GetString(6);
                hdDTO.MaSoThue = reader.GetString(7);
                hdDTO.GiaoDen = reader.GetString(8);
                hdDTO.TraTruoc = reader.GetDouble(9);
                hdDTO.ConNo = reader.GetDouble(10);
                hdDTO.PhiGiaoHang = reader.GetDouble(11);
                hdDTO.ThanhTien = reader.GetDouble(12);
                hdDTO.TinhTrang = reader.GetInt32(13);
                hdDTO.LoaiHoaDon = reader.GetInt32(14);
                danhsach.Add(hdDTO);
            }
            conn.Close();
            return danhsach;
        }

        public List<HoaDonDTO> Tra_cuu_hoa_don(string mahoadon, string tenkhachhang, DateTime tungay, DateTime denngay)
        
        {
            List<HoaDonDTO> danhsach = new List<HoaDonDTO>();
            //string sql = "SELECT * FROM HOADON WHERE MaHoaDon LIKE '%' + @mhd + '%' ";
            string sql = "SELECT HOADON.*, KHACHHANG.TenKhachHang FROM HOADON, KHACHHANG WHERE HOADON.MaKhachHang = KHACHHANG.MaKhachHang AND NgayLap BETWEEN '" + tungay + "' AND '" + denngay + "' ";
            if (mahoadon != "")
                sql = sql + " AND MaHoaDon = '" + Convert.ToInt32(mahoadon) + "' ";
            if (tenkhachhang != "")
                sql = sql + " AND KHACHHANG.TenKhachHang = '" + tenkhachhang + "' ";
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@mahoadon", SqlDbType.NVarChar).Value = mahoadon;
            cmd.Parameters.Add("@tenkhachhang", SqlDbType.NVarChar).Value = tenkhachhang;
            cmd.Parameters.Add("@tungay", SqlDbType.DateTime).Value = tungay;
            cmd.Parameters.Add("@denngay", SqlDbType.DateTime).Value = denngay;
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHoaDon = reader.GetInt32(0);
                hdDTO.MaKhachHang = reader.GetInt32(1);
                hdDTO.NgayLap = reader.GetDateTime(2);
                hdDTO.Vat = reader.GetString(3);
                hdDTO.NgayGiao = reader.GetDateTime(4);
                hdDTO.TenNguoiNhan = reader.GetString(5);
                hdDTO.SdtNguoiNhan = reader.GetString(6);
                hdDTO.MaSoThue = reader.GetString(7);
                hdDTO.GiaoDen = reader.GetString(8);
                hdDTO.TraTruoc = reader.GetDouble(9);
                hdDTO.ConNo = reader.GetDouble(10);
                hdDTO.PhiGiaoHang = reader.GetDouble(11);
                hdDTO.ThanhTien = reader.GetDouble(12);
                hdDTO.TinhTrang = reader.GetInt32(13);
                hdDTO.LoaiHoaDon = reader.GetInt32(14);
                danhsach.Add(hdDTO);
            }
            conn.Close();
            return danhsach;
        }


        //END KHANG
    }
}
